Spring之十 整合Sqlite - - CSDN博客
创建时间:
2017/7/28 11:41
来源:
http://blog.csdn.net/icanhaha/article/details/51016477
1.加入jar包
sqlite-jdbc-3.8.11.2.jar
2.applicationContext.xml配置文件中配置
[html]
view plain
copy
<!-- sqlite内存数据库连接池-->
<
bean
id
=
"sqliteDataSource"
class
=
"org.apache.tomcat.jdbc.pool.DataSource"
destroy-method
=
"close"
>
<
property
name
=
"poolProperties"
>
<
bean
class
=
"org.apache.tomcat.jdbc.pool.PoolProperties"
>
<
property
name
=
"driverClassName"
value
=
"org.sqlite.JDBC"
/>
<
property
name
=
"url"
value
=
"jdbc:sqlite:hello.db"
/>
<
property
name
=
"minIdle"
value
=
"10"
/>
<
property
name
=
"maxActive"
value
=
"100"
/>
<!-- 数据库连接池配置 -->
<!-- 初始化连接数量 -->
<
property
name
=
"initialSize"
value
=
"50"
/>
<!-- 最大连接数量 -->
<!-- 最小空闲连接数量 -->
<
property
name
=
"removeAbandoned"
value
=
"true"
/>
<
property
name
=
"removeAbandonedTimeout"
value
=
"600"
/>
<!-- 验证连接是否有效 -->
<
property
name
=
"validationQuery"
value
=
"select 1"
/>
<!-- 验证失败时,是否将连接从池中丢弃 -->
<
property
name
=
"testWhileIdle"
value
=
"true"
/>
<!-- 把空闲时间超过minEvictableIdleTimeMillis毫秒的连接断开,直到连接池中的连接数到minIdle为止(毫秒,30分钟) -->
<
property
name
=
"timeBetweenEvictionRunsMillis"
value
=
"1200000"
/>
<!-- 连接池中连接可空闲的时间(毫秒,5分钟) -->
<
property
name
=
"minEvictableIdleTimeMillis"
value
=
"1800000"
/>
<!-- 在每次空闲连接回收器线程(如果有)运行时检查的连接数量 -->
<
property
name
=
"numTestsPerEvictionRun"
value
=
"5"
/>
</
bean
>
</
property
>
</
bean
>
3.封装SqliteUtil对象
[html]
view plain
copy
@Repository
public class SqliteUtil {
private Logger
logger
=
Logger
.getLogger(this.getClass());
/**
* 数据源
*/
@Resource(
name
=
"sqliteDataSource"
)
private DataSource sqliteDataSource;
/**
* 获取数据库连接
* @return conn
*/
public Connection getConnection() throws SQLException {
Connection
conn
=
sqliteDataSource
.getConnection();
conn.setAutoCommit(false);
return conn;
}
/**
* 关闭数据库连接
* @param conn
*/
public void close(Connection conn, Statement stmt, ResultSet rs) {
if (null != rs) {
try {
rs.close();
} catch (SQLException ex) {
logger.error(null, ex);
}
rs
=
null
;
}
if (null != stmt) {
try {
stmt.close();
} catch (SQLException ex) {
logger.error(null, ex);
}
stmt
=
null
;
}
if (null != rs) {
try {
rs.close();
} catch (SQLException ex) {
logger.error(null, ex);
}
rs
=
null
;
}
}
}
4.封装Dao层
[html]
view plain
copy
@Repository(
value
=
"realtimeDao"
)
public class RealtimeDaoImpl implements IRealtimeDao {
@Resource
private SqliteUtil sqliteUtil;
@Override
public void initRealtime() throws Exception {
Map
<
String
, List
<
String
>
>
tables
=
getFieldNamesByClass
(Realtime.class);
Connection
conn
=
sqliteUtil
.getConnection();
Statement
stmt
=
conn
.createStatement();
StringBuilder
sb
=
new
StringBuilder();
for (String table : tables.keySet()) {
sb.append("CREATE TABLE IF NOT EXISTS ");
sb.append("'").append(table).append("'");
sb.append("(");
for (String column : tables.get(table)) {
sb.append(column).append(" string");
if (column.contains("termSn")) {
sb.append(" PRIMARY KEY UNIQUE");
}
sb.append(",");
}
sb.delete(sb.lastIndexOf(","), sb.lastIndexOf(",") + 1);
sb.append(");");
stmt.addBatch(sb.toString());
sb.delete(0, sb.length());
}
stmt.executeBatch();
conn.commit();
sqliteUtil.close(conn, stmt, null);
}
@Override
public void saveObject(Object obj) throws Exception {
Map
<
String
, List
<
String
>
>
tablesMap
=
getFieldNamesByClass
(Realtime.class);
Map
<
String
, String
>
paramMap
=
getFieldValuesByClass
(obj);
StringBuilder
sql
=
new
StringBuilder();
StringBuilder
value
=
new
StringBuilder();
Connection
conn
=
sqliteUtil
.getConnection();
Statement
stmt
=
conn
.createStatement();
int indexFlag;
//组装每个table的字段
for (String table : tablesMap.keySet()) {
String
sqlStr
=
this
.buildSaveSql(table, tablesMap, paramMap, sql, value);
stmt.addBatch(sqlStr);
sql.delete(0, sql.length());
value.delete(0, value.length());
}
stmt.executeBatch();
conn.commit();
sqliteUtil.close(conn, stmt, null);
}
@Override
public void saveObjectList(List
<?
>
objList) throws Exception {
int
listSize
=
objList
.size() - 1;
int
batchFlag
=
0
;
Map
<
String
, List
<
String
>
>
tablesMap
=
getFieldNamesByClass
(objList.get(0).getClass());
Map
<
String
, String
>
paramMap;
List
<
String
>
sqlList
=
new
ArrayList
<
String
>
();
StringBuilder
sql
=
new
StringBuilder();
StringBuilder
value
=
new
StringBuilder();
Connection
conn
=
sqliteUtil
.getConnection();
Statement
stmt
=
conn
.createStatement();
for (int
i
=
0
; i
<
= listSize; i++) {
Object
obj
=
objList
.remove(0);
paramMap
=
getFieldValuesByClass
(obj);
//组装每个table的字段
for (String table : tablesMap.keySet()) {
String
sqlStr
=
this
.buildSaveSql(table, tablesMap, paramMap, sql, value);
stmt.addBatch(sqlStr);
sql.delete(0, sql.length());
value.delete(0, value.length());
if (
batchFlag
== 5000 ||
i
== listSize) {
stmt.executeBatch();
batchFlag++;
}
}
}
conn.commit();
sqliteUtil.close(conn, stmt, null);
}
@Override
public Map
<
String
, String
>
findOneObject(String termSn, Class clazz) throws Exception {
Map
<
String
, List
<
String
>
>
tablesMap
=
getFieldNamesByClass
(clazz);
StringBuilder
sql
=
new
StringBuilder();
StringBuilder
field
=
new
StringBuilder();
int
tableSize
=
tablesMap
.keySet().size();
int
index
=
0
;
for (String table : tablesMap.keySet()) {
if (index != 0) {
sql.append(" LEFT JOIN ").append("'").append(table).append("' t").append(index).append(" ON ").append("
t0.termSn
=t
").append(index).append("
.termSn");
} else {
sql.append(" FROM ").append("'").append(table).append("' t").append(index);
}
field.append(" t").append(index).append(".*").append(",");
index++;
}
sql.append(" WHERE
t0.termSn
='").append(termSn).append("'
");//.append("
' LIMIT 0,1");
field.delete(field.lastIndexOf(","), field.lastIndexOf(",") + 1);
sql.insert(0, field);
sql.insert(0, "SELECT ");
field
=
null
;
Connection
conn
=
sqliteUtil
.getConnection();
Statement
stmt
=
conn
.createStatement();
ResultSet
rs
=
stmt
.executeQuery(sql.toString());
Map
<
String
, String
>
valueMap
=
new
HashMap
<
String
, String
>
();
if (rs.next()) {
ResultSetMetaData
data
=
rs
.getMetaData();
int
columnSize
=
data
.getColumnCount() - 1;
for (int
i
=
1
; i
<
= columnSize; i++) {
valueMap.put(data.getColumnName(i), rs.getString(data.getColumnName(i)));
}
}
return valueMap;
}
/**
* 组装多个表的sql
*/
private String buildSaveSql(String table, Map
<
String
, List
<
String
>
>
tablesMap, Map
<
String
, String
>
paramMap,
StringBuilder sql, StringBuilder values) {
sql.append("REPLACE INTO ");
sql.append("'").append(table).append("'");
sql.append("(");
values.append(" VALUES(");
//组装字段值
for (String column : tablesMap.get(table)) {
if (paramMap.containsKey(column)) {
sql.append(column).append(",");
values.append("'").append(paramMap.get(column)).append("',");
}
}
int
indexFlag
=
values
.lastIndexOf(",");
values.delete(indexFlag, indexFlag + 1);
values.append(")");
indexFlag
=
sql
.lastIndexOf(",");
sql.delete(indexFlag, indexFlag + 1);
sql.append(")").append(values).append(";");
return sql.toString();
}
/**
* 根据一个类,获取当前类及子类的字段列表
* @param clazz
* @return
* @throws Exception
*/
private Map
<
String
, List
<
String
>
>
getFieldNamesByClass(Class clazz) throws Exception {
Map
<
String
, List
<
String
>
>
paramMap
=
new
HashMap
<
String
, List
<
String
>
>
();
String
className
=
clazz
.getName();
paramMap.put(className, new ArrayList
<
String
>
());
Field[]
fs
=
clazz
.getDeclaredFields();
List
<
Field
>
list
=
new
ArrayList
<
Field
>
();
list.addAll(Arrays.asList(fs));
for (Field field : list) {
Class
classes
=
field
.getType();
if (!classes.getName().contains("com.ithings.")) {
paramMap.get(className).add(field.getName());
} else {
//自定义类型
Map
<
String
, List
<
String
>
>
childMap
=
getFieldNamesByClass
(classes);
paramMap.putAll(childMap);
}
}
return paramMap;
}
/**
* 获取一个对象及子对象的字段值(属性名/属性值)
*/
private Map
<
String
, String
>
getFieldValuesByClass(Object obj) throws Exception {
Map
<
String
, String
>
paramMap
=
new
HashMap
<
String
, String
>
();
String
className
=
obj
.getClass().getName();
Field[]
fs
=
obj
.getClass().getDeclaredFields();
List
<
Field
>
list
=
new
ArrayList
<
Field
>
();
list.addAll(Arrays.asList(fs));
for (Field field : list) {
field.setAccessible(true);
Object
fdValue
=
field
.get(obj);
if (
null
== fdValue) {
continue;
}
Class
classes
=
field
.getType();
if (!classes.getName().contains("com.ithings.")) {
paramMap.put(field.getName(), fdValue.toString());
} else {
//自定义类型
Map
<
String
, String
>
childMap
=
getFieldValuesByClass
(fdValue);
paramMap.putAll(childMap);
}
}
return paramMap;
}
}
注:
Sqlite教程